﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using SaaS;
using System.Xml;
using System.Xml.XPath;
using Microsoft.WindowsAzure.ServiceRuntime;
using System.IO;


public partial class DataEdit : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        LocalResource myStorage = RoleEnvironment.GetLocalResource("TempStorage");
        string TempObject = Path.Combine(myStorage.RootPath, "TempObject.xml");


        lblTableID.Text = Session["TableID"].ToString();
        lblTableVer.Text = Session["TableVer"].ToString();
        if (ddbDataID.Items.Count == 0)
        {
            MiniSaaSDB db = new MiniSaaSDB();
            db.open();
            if (db.isOpen())
            {
                System.Data.SqlClient.SqlDataReader reader;
                int TableId = int.Parse(lblTableID.Text.Trim());
                int TableVer = int.Parse(lblTableVer.Text.Trim());
                string LoginID = Session["LoginID"].ToString();
                reader = db.executeQuery("SELECT Data_ID FROM DATA WHERE Table_ID = " + TableId + ";");
                if (!reader.HasRows)
                    lblErr.Text = "No customer DATA associated!";
                else
                {
                    ddbDataID.Items.Insert(0,new ListItem("",""));
                    while (reader.Read())
                    {
                        ddbDataID.Items.Add(reader["Data_ID"].ToString());
                    }
                }
                reader.Close();
            }
        }

        System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
        doc.LoadXml(Session["xml"].ToString());
        doc.Save(TempObject);
        XmlDataSource2.DataFile = TempObject;
        XmlDataSource2.XPath = "//col/*/text()";

        DataSet dataSet = new DataSet();
        dataSet.ReadXml(TempObject);
        this.GridView2.DataMember = "col";
        this.GridView2.DataSource = dataSet;
        this.GridView2.DataBind();

    }
    //check all columns
    protected bool checkPriKeyConstraints(string columns, string row)
    {
        return checkPriKeyConstraints(columns, row, -1, false, 0);
    }
    //check specific column only
    protected bool checkPriKeyConstraints(string columns, string row, int colCheck, bool update, int dataID)
    {
        XmlDocument xdata = new XmlDocument();
        xdata.LoadXml(row);
        XmlDocument xcols = new XmlDocument();
        xcols.LoadXml(columns);

        System.Collections.Generic.List<string> priCols = new System.Collections.Generic.List<string>();
        XmlNode xcolNode = xcols.SelectSingleNode("//columns");
        //save column IDs of primary keys
        foreach (XmlNode xn in xcolNode)
        {
            XmlNode xpk = xn.SelectSingleNode("//priKey");
            if (xpk != null && xpk.InnerText.Equals("True", StringComparison.CurrentCultureIgnoreCase))
            {
                XmlNode xcolid = xn.SelectSingleNode("//columnID");
                if (xcolid != null)
                    priCols.Add(xcolid.InnerText);
            }
        }
        if (priCols.Count <= 0)
        {
            //no primary keys in this table
            return true;
        }
        else
        {
            MiniSaaSDB db = new MiniSaaSDB();
            db.open();
            bool distinct = false;
            bool check = false;
            XmlNode xd = xdata.SelectSingleNode("//data");
            foreach (XmlNode xn in xd)
            {
                XmlNode xcolid = xn.SelectSingleNode("//columnID");
                XmlNode xcoldat = xn.SelectSingleNode("//coldata");
                foreach (string c in priCols)
                {
                    if (xcolid != null && xcoldat != null && xcolid.InnerText.Equals(c))
                    {
                        //see if value is distinct
                        if (colCheck < 0 || Convert.ToInt32(xcolid.InnerText) == colCheck)
                        {
                            check = true;
                            string query = "SELECT * FROM KEYS k INNER JOIN DATA d ON k.Data_ID " +
                                " = d.Data_ID WHERE d.Table_ID = " + Session["TableID"].ToString() +
                                " AND k.Column_ID = " + c + " AND k.Value = " + xcoldat.InnerText;
                            if (!db.executeQuery(query).HasRows)
                            {
                                if (update)
                                {
                                    query = "UPDATE KEYS SET Value = " + xcoldat.InnerText +
                                        " WHERE Column_ID = " + c + " AND Data_ID = " + dataID.ToString();
                                    db.executeNonQuery(query);
                                }
                                distinct = true;
                                break;
                            }
                        }
                    }
                }
            }
            if (check == false)
                //nothing checked, so we are alright
                return true;
            else
                return distinct;
        }
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        lblErr.Text = "";
        lblSubmitErr.Text = "";

        string newVal = txtNew.Text.Trim();
        int selectID = int.Parse(ddbDataID.SelectedValue.ToString());
        string colId = txtColid.Text.Trim();
        string xmlStr = Session["datXML"].ToString();
        DataUpdate DU = new DataUpdate();
        string newXmlStr = DU.edit(newVal, colId, xmlStr);
        /*
        XmlDocument xd = new XmlDocument();
        xd.LoadXml(Session["datXML"].ToString());
        string xp = "//col[./columnID='" + txtColid.Text.Trim() + "']/coldata";
        XmlNode xn = xd.SelectSingleNode(xp);
        string oldValue = xn.InnerText;
        xn.InnerText = newVal;
        int selectID = int.Parse(ddbDataID.SelectedValue.ToString());

        System.IO.StringWriter sw = new System.IO.StringWriter();
        XmlTextWriter xw = new XmlTextWriter(sw);
        xd.WriteTo(xw);
        string xmlStr = sw.ToString();
        */

        //Check Primary key restraints
        //The Session["xml"] should hold the columns definition for the selected table
        if (checkPriKeyConstraints(Session["xml"].ToString(), newXmlStr, Convert.ToInt32(txtColid.Text.Trim()), true, selectID) == false)
        {
            lblSubmitErr.Text = "Entered value violates primary key constraints";
            return;
        }

        Session["datXML"] = newXmlStr;

        MiniSaaSDB db = new MiniSaaSDB();
        db.open();
        if (db.isOpen())
        {
            int rows;
            string cmd = "UPDATE DATA SET Data_Rows = '" + newXmlStr + "' WHERE Data_ID = " + selectID + ";";
            rows = db.executeNonQuery(cmd);

            ddbDataID.SelectedIndex = 0;

            this.GridView1.DataSource = "";
            this.GridView1.DataBind();

            txtNew.Text = "";
            txtColid.Text = "";

            lblErr.Text = "Change Success!";

        }
    }
    protected void ddbDataID_SelectedIndexChanged(object sender, EventArgs e)
    {
        LocalResource myStorage = RoleEnvironment.GetLocalResource("TempStorage");
        string TempObject = Path.Combine(myStorage.RootPath, "TempObject.xml");

        lblErr.Text = "";
        lblSubmitErr.Text = "";
        if (ddbDataID.SelectedIndex > 0)
        {
            int selectID = int.Parse(ddbDataID.SelectedValue.ToString());
        
            MiniSaaSDB db = new MiniSaaSDB();
            db.open();
            if (db.isOpen())
            {
                System.Data.SqlClient.SqlDataReader reader;
                string cmd = "SELECT Data_Rows FROM DATA WHERE Data_ID = " + selectID + ";";
                reader = db.executeQuery(cmd);
                reader.Read();
                string datRows = reader["Data_Rows"].ToString();
                Session["datXML"] = datRows;
                reader.Close();

                System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
                doc.LoadXml(datRows);
                doc.Save(TempObject);
                XmlDataSource1.DataFile = TempObject;
                XmlDataSource1.XPath = "//col/*/text()";

                DataSet dataSet = new DataSet();
                dataSet.ReadXml(TempObject);
                this.GridView1.DataMember = "col";
                this.GridView1.DataSource = dataSet;
                this.GridView1.DataBind();
            }            

        }

    }
    protected void btnDel_Click(object sender, EventArgs e)
    {
        int selectID = int.Parse(ddbDataID.SelectedValue.ToString());
        MiniSaaSDB db = new MiniSaaSDB();
        db.open();
        if (db.isOpen())
        {
            int rows;
            string cmd = "DELETE KEYS WHERE Data_ID = " + selectID + ";";
            rows = db.executeNonQuery(cmd);
            cmd = "DELETE DATA WHERE Data_ID = " + selectID + ";";

            string toDel = ddbDataID.SelectedValue.ToString();
            ddbDataID.Items.Remove(toDel);
            ddbDataID.SelectedIndex = 0;
           
            this.GridView1.DataSource = "";
            this.GridView1.DataBind();
        }           
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox t1 = new TextBox();
        Label t2 = new Label();

        t1 = (TextBox)GridView1.Rows[Convert.ToInt16(HiddenField1.Value)].Cells[1].Controls[0];
        t2 = (Label)GridView1.Rows[Convert.ToInt16(HiddenField1.Value)].FindControl("Label1");

        lblErr.Text = "";
        lblSubmitErr.Text = "";

        string newVal = t1.Text;
        int selectID = int.Parse(ddbDataID.SelectedValue.ToString());
        string colId = t2.Text;
        string xmlStr = Session["datXML"].ToString();
        DataUpdate DU = new DataUpdate();
        string newXmlStr = DU.edit(newVal, colId, xmlStr);
      
        //Check Primary key restraints
        //The Session["xml"] should hold the columns definition for the selected table
        if (checkPriKeyConstraints(Session["xml"].ToString(), newXmlStr, Convert.ToInt32(t2.Text), true, selectID) == false)
        {
            lblSubmitErr.Text = "Entered value violates primary key constraints";
            return;
        }

        Session["datXML"] = newXmlStr;

        MiniSaaSDB db = new MiniSaaSDB();
        db.open();
        if (db.isOpen())
        {
            int rows;
            string cmd = "UPDATE DATA SET Data_Rows = '" + newXmlStr + "' WHERE Data_ID = " + selectID + ";";
            rows = db.executeNonQuery(cmd);

           // ddbDataID.SelectedIndex = 0;

           // this.GridView1.DataSource = "";
           // this.GridView1.DataBind();

            //txtNew.Text = "";
            //txtColid.Text = "";

            lblErr.Text = "Change Success!";

        }

        GridView1.EditIndex = -1;

        DataSet dataSet = new DataSet();
        System.IO.StringReader xmlSR = new System.IO.StringReader(Session["datXML"].ToString());
        dataSet.ReadXml(xmlSR);

        GridView1.DataMember = "col";
        GridView1.DataSource = dataSet;
        GridView1.DataBind();
        
        
    }

    protected void GridView2_RowEditing(object sender, GridViewEditEventArgs e)
    {

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = Convert.ToInt16(e.NewEditIndex);


        HiddenField1.Value = e.NewEditIndex.ToString();

        DataSet dataSet = new DataSet();
        System.IO.StringReader xmlSR = new System.IO.StringReader(Session["datXML"].ToString());
        dataSet.ReadXml(xmlSR);

        GridView1.DataMember = "col";
        GridView1.DataSource = dataSet;
        GridView1.DataBind();
    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        DataSet dataSet = new DataSet();
        System.IO.StringReader xmlSR = new System.IO.StringReader(Session["datXML"].ToString());

        dataSet.ReadXml(xmlSR);
        GridView1.DataMember = "col";
        GridView1.DataSource = dataSet;
        GridView1.DataBind();
    }
}
